﻿//引入MD5、MD5CryptoServiceProvider类的命名空间
using System.Data;
using System.Security.Cryptography;
//引入事务处理类TransactionScope的命名空间
using System.Transactions;
using IceCS.GoodsSale.Models.GoodsModels;
using IceCS.GoodsSale.Models.UserModels;
using Microsoft.Data.SqlClient;
//引入Model实体类的命名空间

namespace IceCS.GoodsSale.DAL
{
    public class PublicMethodsAccesser
    {
        //使用MD5加密用户密码的方法，返回32位的密文字符串
        public string GetMd5Str(string myString)
        {
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] fromData = System.Text.Encoding.Unicode.GetBytes(myString);
            byte[] toData = md5.ComputeHash(fromData);
            string byteStr = null;
            for (int i = 0; i < toData.Length; i++)
            {
                byteStr += toData[i].ToString("x2");
            }
            return byteStr;
        }

        //自定义的加密密码保护信息的方法
        public string EnCode(string str)
        {
            string enText = "";
            for (int i = 0; i < str.Length; i++)
            {
                enText = enText + (char)(str[i] + i);
            }
            return enText;
        }

        //解密密码保护信息的方法
        public string DeCode(string str)
        {
            string deText = "";
            for (int i = 0; i < str.Length; i++)
            {
                deText = deText + (char)(str[i] - i);
            }
            return deText;
        }

        //添加用户和用户权限信息的方法，采用的TransactionScope类进行事务处理
        public bool InsertUserToTwoTable(User user, UserPurview userPurview)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                string md5UserPassword = GetMd5Str(user.UserPassword);
                string sqlStr1 = "insert into TB_User values(@UserName,@UserPassword,@UserType)";
                SqlParameter[] param1 = new SqlParameter[]
                {
                    new SqlParameter("@UserName",user.UserName),
                    new SqlParameter("@UserPassword",md5UserPassword),
                    new SqlParameter("@UserType","一般用户"),
                };
                DBHelper.Conn.Open();
                SqlCommand cmd1 = new SqlCommand(sqlStr1, DBHelper.Conn);
                cmd1.Parameters.AddRange(param1);
                cmd1.ExecuteNonQuery();
                string sqlStr2 = "insert into TB_UserPurview values(@UserName,@UserManage,@PurchaseGoods,@GoodsQuery,@GoodsSale,@SaleDataQuery,@SaleDataCount,@InventoryAlarm,@InventoryQuery,@BackupData,@RestoreData)";
                SqlParameter[] param2 = new SqlParameter[]
                {
                    new SqlParameter("@UserName",userPurview.UserName),
                    new SqlParameter("@UserManage","否"),
                    new SqlParameter("@PurchaseGoods","否"),
                    new SqlParameter("@GoodsQuery","否"),
                    new SqlParameter("@GoodsSale","否"),
                    new SqlParameter("@SaleDataQuery","否"),
                    new SqlParameter("@SaleDataCount","否"),
                    new SqlParameter("@InventoryAlarm","否"),
                    new SqlParameter("@InventoryQuery","否"),
                    new SqlParameter("@BackupData","否"),
                    new SqlParameter("@RestoreData","否"),
                };
                SqlCommand cmd2 = new SqlCommand(sqlStr2, DBHelper.Conn);
                cmd2.Parameters.AddRange(param2);
                cmd2.ExecuteNonQuery();
                ts.Complete();
                DBHelper.Conn.Close();
                return true;
            }
        }

        //删除用户、用户权限和对应的密码保护信息的方法，进行了事务处理
        public bool DeleteUserFromThreeTable(string userName)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                string sqlStr1 = "delete from TB_UserPswProInfo where UserName='" + userName + "'";
                DBHelper.Conn.Open();
                SqlCommand cmd1 = new SqlCommand(sqlStr1, DBHelper.Conn);
                cmd1.ExecuteNonQuery();
                string sqlStr2 = "delete from TB_UserPurview where UserName='" + userName + "'";
                SqlCommand cmd2 = new SqlCommand(sqlStr2, DBHelper.Conn);
                cmd2.ExecuteNonQuery();
                string sqlStr3 = "delete from TB_User where UserName='" + userName + "'";
                SqlCommand cmd3 = new SqlCommand(sqlStr3, DBHelper.Conn);
                cmd3.ExecuteNonQuery();
                ts.Complete();
                DBHelper.Conn.Close();
                return true;
            }
        }

        //销售商品时添加销售信息和修改库存的方法，进行了事务处理
        public bool InsertSaleDataUpdateInventory(Sale sale, int inventory)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                string sqlStr1 = "insert into TB_Sale values(@GoodsID,@Quantity,@TotalPrice,@Earnings,@Date,@Seller)";
                SqlParameter[] param1 = new SqlParameter[]
                {
                     new SqlParameter("@GoodsID",sale.GoodsID),
                     new SqlParameter("@Quantity",sale.Quantity),
                     new SqlParameter("@TotalPrice",sale.TotalPrice),
                     new SqlParameter("@Earnings",sale.Earnings),
                     new SqlParameter("@Date",sale.Date),
                     new SqlParameter("@Seller",sale.Seller),
                };
                DBHelper.Conn.Open();
                SqlCommand cmd1 = new SqlCommand(sqlStr1, DBHelper.Conn);
                cmd1.Parameters.AddRange(param1);
                cmd1.ExecuteNonQuery();
                string sqlStr2 = "update TB_Goods set Inventory=@Inventory where GoodsID=@GoodsID";
                SqlParameter[] param2 = new SqlParameter[]
                {
                     new SqlParameter("@GoodsID",sale.GoodsID),
                     new SqlParameter("@Inventory",inventory),
                };
                SqlCommand cmd2 = new SqlCommand(sqlStr2, DBHelper.Conn);
                cmd2.Parameters.AddRange(param2);
                cmd2.ExecuteNonQuery();
                ts.Complete();
                DBHelper.Conn.Close();
                return true;
            }
        }

        //备份数据库的方法
        public bool BackupDB(string backupPath)
        {
            string sqlStr;
            sqlStr = "backup database DB_GoodsSale to disk='" + backupPath + " '";
            SqlCommand cmd = new SqlCommand(sqlStr, DBHelper.Conn);
            DBHelper.Conn.Open();
            cmd.ExecuteNonQuery();
            DBHelper.Conn.Close();
            return true;
        }

        //恢复数据库的方法
        public bool RestoreDB(string serverName, string restorePath)
        {
            SqlConnection.ClearAllPools();
            string conStr = "Data Source=" + serverName + ";Initial Catalog=master;Integrated Security=True";
            DBHelper.Conn.ConnectionString = conStr;
            DBHelper.Conn.Open();
            string sqlStr = "select spid from master..sysprocesses where dbid=db_id( 'DB_GoodsSale') ";
            SqlDataAdapter sda = new SqlDataAdapter(sqlStr, DBHelper.Conn);
            DataTable spidTable = new DataTable();
            sda.Fill(spidTable);
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = DBHelper.Conn;
            for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
            {
                cmd1.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();
                cmd1.ExecuteNonQuery();
            }
            DBHelper.Conn.Close();
            DBHelper.Conn.Dispose();
            string restoreStr = "use master restore database DB_GoodsSale from disk='" + restorePath + "'" + " with replace";
            DBHelper.Conn.Open();
            SqlCommand cmd2 = new SqlCommand(restoreStr, DBHelper.Conn);
            cmd2.ExecuteNonQuery();
            DBHelper.Conn.Close();
            return true;
        }
    }
}
